﻿using Cocon90.Lib.Dal;
using Cocon90.Lib.Dal.Rule;
using Cocon90.Lib.Dal.Tools;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Demo
{
    public partial class sqlHelperDemo : Form
    {
        public sqlHelperDemo()
        {
            InitializeComponent();
        }
        Cocon90.Lib.Dal.IDataHelper db = Cocon90.Lib.Dal.DataHelperFactory.CreateInstence();
        private void getOrderBySql_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            lbBody.Text = sqlHelper.getOrderBySql("select * from Person.Address", "AddressID", false);
            dgv.DataSource = db.getTable(lbBody.Text);
        }

        private void getPagedSql_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            lbBody.Text = sqlHelper.getPagedSql("select * from Person.Address", "AddressID", true, 2, 60);
            dgv.DataSource = db.getTable(lbBody.Text);
        }

        private void 添加ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            lbBody.Text += sqlHelper.getAddSql("value1", "value2", true, 1, 30, DateTime.Now) + "\r\n";
            List<string> lis = new List<string>();
            lis.Add("Column1");
            lis.Add("Column2");
            lis.Add("Column3");
            lbBody.Text += sqlHelper.getAddSqlParam(lis);
        }

        private void 添加语句ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            columnValueDics.Add("AddressLine1", "Chinapsu" + DateTime.Now.Millisecond);
            columnValueDics.Add("AddressLine2", "JiNan" + DateTime.Now.Millisecond);
            columnValueDics.Add("City", "JiNanCity"+DateTime.Now.Millisecond);
            columnValueDics.Add("StateProvinceID", 79);
            columnValueDics.Add("PostalCode", "250104");
            columnValueDics.Add("rowguid", Guid.NewGuid());
            columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getInsertSql("Person.Address", columnValueDics);
            db.execNoQuery(lbBody.Text);
            dgv.DataSource = db.getTable(sqlHelper.getPagedSql("select * from Person.Address", "AddressID", false, 1, 30));
        }

        private void 带参的添加语句ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<string> columnNameList = new List<string>();
            columnNameList.Add("Column1");
            columnNameList.Add("Column2");
            columnNameList.Add("Column3");
            columnNameList.Add("Column4");
            lbBody.Text = sqlHelper.getInsertSqlParam("Person.Address", columnNameList);

        }

        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            columnValueDics.Add("AddressLine1", "Chinapsu");
            columnValueDics.Add("AddressLine2", "JiNan");
            columnValueDics.Add("City", "JiNanCity");
            columnValueDics.Add("StateProvinceID", 79);
            columnValueDics.Add("PostalCode", "250104");
            columnValueDics.Add("rowguid", Guid.NewGuid());
            columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getEditSql(columnValueDics) + "\r\n";


            List<string> columnNameList = new List<string>();
            columnNameList.Add("Column1");
            columnNameList.Add("Column2");
            columnNameList.Add("Column3");
            columnNameList.Add("Column4");
            lbBody.Text += sqlHelper.getEditSqlParam(columnNameList);

        }

        private void 修改语句ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            //columnValueDics.Add("AddressLine1", "Chinapsu");
            columnValueDics.Add("AddressLine2", "JiNan3");
            columnValueDics.Add("City", null);
            //columnValueDics.Add("StateProvinceID", 79);
            //columnValueDics.Add("PostalCode", "250104");
            //columnValueDics.Add("rowguid", Guid.NewGuid());
            //columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getUpdateSql("Person.Address", columnValueDics, "AddressID=1");
        }

        private void 带参数修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<string> columnNameList = new List<string>();
            columnNameList.Add("Column1");
            columnNameList.Add("Column2");
            columnNameList.Add("Column3");
            columnNameList.Add("Column4");
            lbBody.Text += sqlHelper.getUpdateSqlParam("Person.Address", columnNameList, null);
        }

        private void 条件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            columnValueDics.Add("AddressLine1", "Chinapsu");
            columnValueDics.Add("AddressLine2", "JiNan3");
            columnValueDics.Add("City", "JiNanCity");
            columnValueDics.Add("StateProvinceID", 79);
            columnValueDics.Add("PostalCode", "250104");
            columnValueDics.Add("rowguid", Guid.NewGuid());
            columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getWhereSql(columnValueDics);
        }

        private void 带参数条件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<string> columnNameList = new List<string>();
            columnNameList.Add("Column1");
            columnNameList.Add("Column2");
            columnNameList.Add("Column3");
            columnNameList.Add("Column4");
            lbBody.Text += sqlHelper.getWhereSqlParam(columnNameList);

        }

        private void 带参数条件EntryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<AliseEntry> columnNameList = new List<AliseEntry>();
            columnNameList.Add(new AliseEntry { Name = "Column1", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column2", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column1", Alise = "表2" });
            columnNameList.Add(new AliseEntry { Name = "Column1", Alise = "表3" });
            lbBody.Text += sqlHelper.getWhereSqlParam(columnNameList);
        }
        private void likeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            columnValueDics.Add("AddressLine1", "Chinapsu");
            columnValueDics.Add("AddressLine2", "JiNan3");
            columnValueDics.Add("City", "JiNanCity");
            columnValueDics.Add("StateProvinceID", 79);
            columnValueDics.Add("PostalCode", "250104");
            columnValueDics.Add("rowguid", Guid.NewGuid());
            columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getWhereLikeSql(columnValueDics);
        }
        private void 别名ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, string> columnAsName = new Dictionary<string, string>();
            columnAsName.Add("AddressLine1", "地址一");
            columnAsName.Add("AddressLine2", "地址二");
            columnAsName.Add("City", "城市");
            columnAsName.Add("PostalCode", "邮政编码");
            lbBody.Text += sqlHelper.getAliseSql(columnAsName);
        }

        private void 带参数别名ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, string> columnAsName = new Dictionary<string, string>();
            columnAsName.Add("AddressLine1", "地址一");
            columnAsName.Add("AddressLine2", "地址二");
            columnAsName.Add("City", "城市");
            columnAsName.Add("PostalCode", "邮政编码");
            lbBody.Text += sqlHelper.getAliseSql(new string[] { "address", "people", "address", "people" }, columnAsName);
        }

        private void 使用Entry别名ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<AliseEntry> columnNameList = new List<AliseEntry>();
            columnNameList.Add(new AliseEntry { Name = "Column1", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column2", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column21", Alise = "表2" });
            columnNameList.Add(new AliseEntry { Name = "Column32", Alise = "表3" });
            lbBody.Text += sqlHelper.getAliseSql(columnNameList);
        }

        private void 使用EntryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<AliseEntry> columnNameList = new List<AliseEntry>();
            columnNameList.Add(new AliseEntry { Name = "Column1", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column2", Alise = "表1" });
            columnNameList.Add(new AliseEntry { Name = "Column21", Alise = "表2" });
            columnNameList.Add(new AliseEntry { Name = "Column32", Alise = "表3" });
            lbBody.Text += sqlHelper.getAliseSql(new string[] { "address", "people", "address", "people" }, columnNameList);
        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();

            lbBody.Text += sqlHelper.getDeleteSql("Person.Address", "address1='中国'");
        }

        private void 带参数删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            Dictionary<string, object> columnValueDics = new Dictionary<string, object>();
            columnValueDics.Add("AddressLine1", "Chinapsu");
            columnValueDics.Add("AddressLine2", null);
            columnValueDics.Add("ModifiedDate", DateTime.Now);
            lbBody.Text += sqlHelper.getDeleteSqlParam("Person.Address", columnValueDics);
        }

        private void 取得表的主键ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            //包含架构名
            lbBody.Text += sqlHelper.getPrimeryKey("Person.Address");
            lbBody.Text += "\r\nPerson.Address表的主键是:" + db.getString(lbBody.Text);
            lbBody.Text += "\r\n";
            //不包含架构名
            var sql = sqlHelper.getPrimeryKey("Address");
            lbBody.Text += sql;
            lbBody.Text += "\r\nPerson.Address表的主键是:" + db.getString(sql);
        }

        private void 多表连接查询ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<AliseEntry> tbs = new List<AliseEntry>();
            tbs.Add(new AliseEntry { Name = "Person.Address", Alise = "addressTable" });
            tbs.Add(new AliseEntry { Name = "Person.StateProvince", Alise = "stateTable" });
            tbs.Add(new AliseEntry { Name = "Person.CountryRegion", Alise = "countryTable" });

            List<Dictionary<string, string>> alesLst = new List<Dictionary<string, string>>();

            Dictionary<string, string> addressTable = new Dictionary<string, string>();
            addressTable.Add("AddressID", "地址编号");
            addressTable.Add("AddressLine1", "地址1");
            addressTable.Add("AddressLine2", "地址2");
            addressTable.Add("City", "城市");
            //addressTable.Add("StateProvinceID", "国家编号");
            addressTable.Add("PostalCode", "邮政编号");
            addressTable.Add("rowguid", "行标志");
            //addressTable.Add("ModifiedDate", "修改日期");
            alesLst.Add(addressTable);
            Dictionary<string, string> stateTable = new Dictionary<string, string>();
            stateTable.Add("StateProvinceID", "国家编号");
            //stateTable.Add("CountryRegionCode", "地区代码");
            //stateTable.Add("Name", "国家名称");
            stateTable.Add("ModifiedDate", "修改日期");
            alesLst.Add(stateTable);
            Dictionary<string, string> countryTable = new Dictionary<string, string>();
            countryTable.Add("CountryRegionCode", "地区代码");
            countryTable.Add("Name", "国家名称");
            alesLst.Add(countryTable);
            lbBody.Text += sqlHelper.getJoinQuery(tbs.ToArray(), alesLst.ToArray(), "addressTable.StateProvinceID=stateTable.StateProvinceID and stateTable.CountryRegionCode=countryTable.CountryRegionCode");
            //dgv.DataSource = db.getTable(lbBody.Text);
            string sql = sqlHelper.getPagedSql(lbBody.Text, "地址编号", true, 1, 60);
            lbBody.Text += "\r\n\r\n分页后：" + sql;
            dgv.DataSource = db.getTable(sql);
        }

        private void 多表连接带参数查询ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbBody.Clear();
            List<AliseEntry> tbs = new List<AliseEntry>();
            tbs.Add(new AliseEntry { Name = "Person.Address", Alise = "addressTable" });
            tbs.Add(new AliseEntry { Name = "Person.StateProvince", Alise = "stateTable" });
            tbs.Add(new AliseEntry { Name = "Person.CountryRegion", Alise = "countryTable" });


            List<Dictionary<string, string>> alesLst = new List<Dictionary<string, string>>();
            Dictionary<string, string> addressTable = new Dictionary<string, string>();
            addressTable.Add("AddressID", "地址编号");
            addressTable.Add("AddressLine1", "地址1");
            addressTable.Add("AddressLine2", "地址2");
            addressTable.Add("City", "城市");
            //addressTable.Add("StateProvinceID", "国家编号");
            addressTable.Add("PostalCode", "邮政编号");
            addressTable.Add("rowguid", "行标志");
            //addressTable.Add("ModifiedDate", "修改日期");
            alesLst.Add(addressTable);

            Dictionary<string, string> stateTable = new Dictionary<string, string>();
            stateTable.Add("StateProvinceID", "国家编号");
            //stateTable.Add("CountryRegionCode", "地区代码");
            //stateTable.Add("Name", "国家名称");
            stateTable.Add("ModifiedDate", "修改日期");
            alesLst.Add(stateTable);

            Dictionary<string, string> countryTable = new Dictionary<string, string>();
            countryTable.Add("CountryRegionCode", "地区代码");
            countryTable.Add("Name", "国家名称");
            alesLst.Add(countryTable);


            List<AliseEntry> whereLst = new List<AliseEntry>();
            //whereLst.Add(new Entry { Name = "StateProvinceID", Alise = "addressTable" });
            //whereLst.Add(new Entry { Name = "CountryRegionCode", Alise = "stateTable" });
            whereLst.Add(new AliseEntry { Name = "Name", Alise = "countryTable" });

            var param = db.createParameter("@countryTableName", "France");

            lbBody.Text = sqlHelper.getJoinQueryParam(tbs.ToArray(), alesLst.ToArray(), whereLst, "addressTable.StateProvinceID=stateTable.StateProvinceID and stateTable.CountryRegionCode=countryTable.CountryRegionCode");
            dgv.DataSource = db.getTable(lbBody.Text, param);

        }

     

    }
}
